<?php

namespace App\Libraries;

use Carbon\Carbon;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

/**
 * Created by PhpStorm.
 * User: Zhangxy
 * Date: 2018/8/31
 * Time: 10:35
 */
class ExcelTool
{

    /**
     * excel 导出
     * 这是使用 PHPOffice/PhpSpreadsheet 库的一个实现 文档参考 https://phpspreadsheet.readthedocs.io/en/develop/
     * 使用 Laravel Excel 这个库 可能会更简单
     * 但是他又引入了 export 这个概念 有条件的获取可能还需要用到 FromQuery
     * 可能是个好东西(目前更倾向于用 PhpSpreadsheet 直观的方式) 文档请参考 https://laravel-excel.maatwebsite.nl/3.0/getting-started/
     *
     * 示例数据
     * $header = ['学号', '姓名', '成绩'];
     * $data = [
     *      ['10001', 'AAAAA', '99'],
     *      ['10002', 'BBBBB', '92'],
     * ];
     *
     * 或者
     *
     *  $header = [
     *      'student_id' => '学号',
     *      'username' => '姓名',
     *      'score' => '成绩'
     *  ];
     *
     *  $data = [
     *      ['username' => 'AAAAA', 'score' => '99', 'student_id' => '10001'],
     *      [ 'username' => 'BBBBB', 'score' => '92','student_id' => '10002'],
     *  ];
     *
     * @param mixed $header 头部
     * @param mixed $data 数据
     * @param string $title 标题
     * User: Zhangxy
     * Date: 2018-08-31
     */
    public function export($header, $data, $title = '')
    {
        $dateTime = Carbon::now()->toDateTimeString();
        if (empty($title)) {
            $title = $dateTime;
            $filename = sprintf("file_name_%s", $dateTime);
        } else {
            $filename = sprintf("%s_%s", $title, $dateTime);
        }

        $spreadsheet = new Spreadsheet();

        // 设置文档属性
        $spreadsheet->getProperties()->setCreator('Candy')
            ->setLastModifiedBy('Candy')
            ->setTitle('CoinWorld Document')
            ->setSubject('Candy')
            ->setDescription('')
            ->setKeywords('')
            ->setCategory('');

        $spreadsheet->setActiveSheetIndex(0);

        $activeSheet = $spreadsheet->getActiveSheet();

        // 设置单元格标题
        $columnNo = 1;
        foreach ($header as $column => $hTitle) {
            $activeSheet->setCellValue($this->getCoordinate(1, $columnNo), $hTitle);
            $columnNo++;
        }

        // 设置单元格内容
        $rowNo = 2;
        foreach ($data as $row => $value) {
            $columnNo = 1;
            foreach ($header as $column => $hTitle) {
                $activeSheet->setCellValue($this->getCoordinate($rowNo, $columnNo), $value[$column]);
                $columnNo++;
            }
            $rowNo++;
        }

        $activeSheet->setTitle($title);

        // 设置活动页为第一个   当excel打开的时候就显示第一个
        $spreadsheet->setActiveSheetIndex(0);

        // 输出内容
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');

        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
        header('Cache-Control: cache, must-revalidate');
        header('Pragma: public');

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;

    }

    /**
     * 根据行列返回excel中的位置
     * @param int $row
     * @param int $column
     * User: Zhangxy
     * Date: 2018-8-31
     * @return string
     */
    protected function getCoordinate($row = 1, $column = 1)
    {
        $arr = array(
            0 => 'Z',
            1 => 'A',
            2 => 'B',
            3 => 'C',
            4 => 'D',
            5 => 'E',
            6 => 'F',
            7 => 'G',
            8 => 'H',
            9 => 'I',
            10 => 'J',
            11 => 'K',
            12 => 'L',
            13 => 'M',
            14 => 'N',
            15 => 'O',
            16 => 'P',
            17 => 'Q',
            18 => 'R',
            19 => 'S',
            20 => 'T',
            21 => 'U',
            22 => 'V',
            23 => 'W',
            24 => 'X',
            25 => 'Y',
            26 => 'Z'
        );

        return sprintf("%s%s", $arr[$column], $row);
    }


}